﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Data;

namespace University.Workshop.DataAccess
{
    public static class SaleDataAccess
    {
        const string ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Pharmacy;Integrated Security=True";
        
        public static bool SaleRegister(Sale sale)
        {
            string sqlSentence = "INSERT INTO Sale VALUES(@Code,@CodeClient,@CodeUser,@DateSale,@TotalPriceSale)";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (sale == null)
                throw new BusinessException("nothing null");
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@Code", sale.Code);
                cmd.Parameters.AddWithValue("@CodeClient", sale.ClientCode);
                cmd.Parameters.AddWithValue("@CodeUser", sale.UserCode);
                cmd.Parameters.AddWithValue("@DateSale", sale.DateSale);
                cmd.Parameters.AddWithValue("@TotalPriceSale", Convert.ToDouble(sale.TotalAmount, System.Globalization.CultureInfo.CurrentCulture));
                result = cmd.ExecuteNonQuery();
                if (result == 0)
                    return false;
                else
                    return true;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }

        public static bool SaleDetailRegister(SaleDetail saleDetail)
        {
            string sqlSentence = "INSERT INTO SaleDetail VALUES(@CodeSale,@CodeProduct,@UnitPrice,@Quantity,@TotalPrice)";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (saleDetail == null)
                throw new BusinessException("nothing null");
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@CodeSale",saleDetail.CodeSale);
                cmd.Parameters.AddWithValue("@CodeProduct", saleDetail.CodeProduct);
                cmd.Parameters.AddWithValue("@UnitPrice", saleDetail.UnitPrice);
                cmd.Parameters.AddWithValue("@Quantity", saleDetail.Quantity);
                cmd.Parameters.AddWithValue("@TotalPrice", saleDetail.TotalPrice);
                result = cmd.ExecuteNonQuery();
                if (result == 0)
                {
                    return false;
                }
                else
                    UpdateStock(saleDetail.CodeProduct, saleDetail.Quantity);
                    return true;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }

        private static bool UpdateStock(string codeProduct, double quantity)
        {
            string sqlSentence = "UPDATE Stock SET Quantity=Quantity-@Quantity WHERE CodeProduct=@CodeProduct";
            
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            try
            {
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@CodeProduct", codeProduct);
                cmd.Parameters.AddWithValue("@Quantity", quantity);
                result = cmd.ExecuteNonQuery();
                if (result == 0)
                    return false;
                else
                    return true;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }

        public static System.Data.DataTable GetProductRow(string productCode)
        {
            string sqlSentence = "SELECT P.Name,PRE.Description,S.Quantity,S.PriceSale FROM Product P, Stock S, Presentation PRE WHERE P.PresentationCode=PRE.Code and P.Code=S.CodeProduct and P.Code=@code";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;

            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", productCode);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static DataTable GetSaleCode()
        {
            string sqlSentence = "SELECT MAX(CAST(Code AS BigInt)) FROM Sale";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;

            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            } 
        }
        public static DataTable SuggestionProduct(string suggestion)
        {
            string sqlSentence = "SELECT Code,Name,PresentationCode,Provider,Description,RecetMedic FROM Product where (Description LIKE @Suggestion)";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;

            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@Suggestion", suggestion);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
    }
}
